﻿Public Class frmStatAcc
    Dim dsStatAcc As DataSet
    Dim daStatAcc As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub frmStatAcc_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        e.Cancel = True
        Me.Hide()
    End Sub

    Private Sub frmStatistics_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Call gSubIconShow(Me)
        Call dbConnection()
        Call gSubMkCmbYear(cmbYear)
        Call mkCmbAccount()
        cmbInOut.SelectedIndex = 0
        Call grid1Refresh("default")
    End Sub

    Private Sub btnGoStatistics_Click(sender As System.Object, e As System.EventArgs) Handles btnGoStatistics.Click
        Dim result As Integer = MessageBox.Show("계정별 매입매출을 지금 조사 하시겠습니까?", "매입매출 조사", MessageBoxButtons.YesNo)
        If result = DialogResult.No Then
            Exit Sub
        ElseIf result = DialogResult.Yes Then
            btnSearch.Enabled = False
            btnSearchReset.Enabled = False
            Call gSubLookupOutAll()
            Call gSubLookupOutAcc()
            Call gSubLookupInAll()
            Call gSubLookupInAcc()
            MsgBox("매입매출 계정별 현황 조사가 완료 되었습니다")
            btnSearch.Enabled = True
            btnSearchReset.Enabled = True
        End If
    End Sub

    Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
        Call grid1Refresh("")
    End Sub

    Private Sub btnNewWindow_Click(sender As System.Object, e As System.EventArgs) Handles btnNewWindow.Click
        Dim MDIFormStat As New frmStatAcc
        MDIFormStat.ShowDialog()
    End Sub

    Private Sub cmbAccount_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbAccount.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbInOut_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbInOut.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbYear_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbYear.KeyPress
        e.Handled = True
    End Sub

#Region "프로시져"

    Private Sub grid1Refresh(ByVal initFlag As String)
        Call dbConnection()
        Dim addSql As String = ""
        Dim valFindYear As String = ""
        Dim valFindAccount As String = ""
        Dim totalAmt As Double = 0
        Dim totalOrgAmt As Double = 0
        Dim totalRestAmt As Double = 0

        If cmbInOut.SelectedIndex = 0 Then
            addSql = addSql + " AND A.inOut='IN' "
        Else
            addSql = addSql + " AND A.inOut='OT' "
        End If
        If cmbYear.SelectedValue.ToString <> "-전체-" Then
            valFindYear = cmbYear.SelectedValue.ToString
        End If
        If valFindYear <> "" Then
            addSql = addSql + " AND A.Yr='" + valFindYear + "' "
        End If
        If cmbAccount.SelectedValue.ToString <> "0" Then
            valFindAccount = cmbAccount.SelectedValue.ToString
        End If
        If valFindAccount <> "" Then
            addSql = addSql + " AND A.typeCd=" + valFindAccount + " "
        End If

        If initFlag = "default" Then
            addSql = " AND A.flag='N'"
        End If
        dsStatAcc = New DataSet

        sql = "SELECT A.Yr as 년도, IIF(A.typeCd=9999, 'TOTAL', B.accountNm) as 계정, A.m01 as 1월, A.m02 as 2월, A.m03 as 3월, A.m04 as 4월, A.m05 as 5월, A.m06 as 6월, A.m07 as 7월, A.m08 as 8월, A.m09 as 9월, A.m10 as 10월, A.m11 as 11월, A.m12 as 12월, A.seq, A.issueDt as 조사일" & _
            "       FROM tblStatistics A LEFT JOIN tblAccount B ON A.typeCd=B.accountCd " + _
            "       WHERE A.flag='Y' AND (A.types='all' OR A.types='acc') " + addSql + _
            "    ORDER BY A.Yr,IIF(A.typeCd=9999, 'TOTAL', B.accountNm) DESC "
        Try
            daStatAcc = New OleDb.OleDbDataAdapter(sql, dbConn)
            daStatAcc.Fill(dsStatAcc, "StatList")

            grdStatAcc.DataSource = dsStatAcc.Tables("StatList")
            lbGrid1Cnt.Text = "총 " + dsStatAcc.Tables("StatList").Rows.Count.ToString + " 건"

            dbConn.Close()
            Call setGrid1Design()

            grdStatAcc.Columns(1).Frozen = True
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setGrid1Design()

        grdStatAcc.Columns(0).Width = "60"
        grdStatAcc.Columns(1).Width = "150"

        grdStatAcc.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(2).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(2).Width = "90"
        grdStatAcc.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(3).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(3).Width = "90"
        grdStatAcc.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(4).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(4).Width = "90"
        grdStatAcc.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(5).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(5).Width = "90"
        grdStatAcc.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(6).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(6).Width = "90"
        grdStatAcc.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(7).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(7).Width = "90"

        grdStatAcc.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(8).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(8).Width = "90"
        grdStatAcc.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(9).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(9).Width = "90"
        grdStatAcc.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(10).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(10).Width = "90"
        grdStatAcc.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(11).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(11).Width = "90"
        grdStatAcc.Columns(12).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(12).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(12).Width = "90"
        grdStatAcc.Columns(13).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdStatAcc.Columns(13).DefaultCellStyle.Format = "n0"
        grdStatAcc.Columns(13).Width = "90"
        grdStatAcc.Columns(14).Visible = False
        grdStatAcc.Columns(15).Width = "150"

        With grdStatAcc.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleLeft

        End With

        grdStatAcc.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        grdStatAcc.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

        For i = 0 To grdStatAcc.Rows.Count - 1
            If IsDBNull(grdStatAcc.Rows(i).Cells(1).Value) = False Then
                If grdStatAcc.Rows(i).Cells(1).Value = "TOTAL" Then
                    grdStatAcc.Rows(i).DefaultCellStyle.BackColor = Color.LavenderBlush
                    grdStatAcc.Rows(i).DefaultCellStyle.Font = New Font(grdStatAcc.DefaultCellStyle.Font, FontStyle.Bold)

                End If
            End If
        Next

    End Sub

    '## 계정 정보 가져오기
    Private Sub mkCmbAccount()
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim addSql As String = ""
        sql = "SELECT accountNm, accountCd FROM tblAccount WHERE flag='Y' AND [part]='OT' ORDER BY accountNm"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "cmbAccGroup")

        dbConn.Close()
        Dim newDsRow As DataRow = dsCommon.Tables("cmbAccGroup").NewRow
        newDsRow("accountNm") = "-전체-"
        newDsRow("accountCd") = "9999"

        dsCommon.Tables("cmbAccGroup").Rows.Add(newDsRow)

        newDsRow = dsCommon.Tables("cmbAccGroup").NewRow
        newDsRow("accountNm") = "-계정-"
        newDsRow("accountCd") = "0"

        dsCommon.Tables("cmbAccGroup").Rows.Add(newDsRow)
        With cmbAccount
            .DataSource = dsCommon.Tables("cmbAccGroup")
            .DisplayMember = "accountNm"
            .ValueMember = "accountCd"
        End With
        cmbAccount.SelectedIndex = dsCommon.Tables("cmbAccGroup").Rows.Count - 1
    End Sub
#End Region

End Class